﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections;



    public class PDB
    {

        public string ConnStr; 


        public PDB()
        {
            SetDefConnStr();
        }

        public PDB(string str)
        {
            ConnStr = ConfigurationSettings.AppSettings[str];
        }

        private void SetDefConnStr()
        {
            ConnStr = ConfigurationSettings.AppSettings["POS"];
        }




        public void OpenConn(ref SqlConnection Conn)
        {
            try
            {
                if (ConnStr == "" || ConnStr == null)
                {
                    SetDefConnStr();
                }

                Conn.ConnectionString = ConnStr;
                if (Conn.State != ConnectionState.Open)
                {
                    Conn.Open();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public void NewConn(ref SqlConnection Conn)
        {
            try
            {
                if (Conn == null)
                {
                    Conn = new SqlConnection();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }


        public void CloseConn(ref SqlConnection Conn)
        {
            try
            {
                if (Conn != null)
                {
                    if (Conn.State == ConnectionState.Open)
                    {
                        Conn.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }



        public DataSet GetDataSet(ref SqlConnection Conn, string sql)
        {
            SqlDataAdapter dp;
            DataSet ds = new DataSet();
            try
            {
                NewConn(ref Conn);
                if (Conn.State != ConnectionState.Open)
                {
                    OpenConn(ref Conn);
                }

                dp = new SqlDataAdapter(sql, Conn);
                dp.Fill(ds);
                return ds;

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public DataTable GetDataTable(ref SqlConnection Conn, string sql)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter dp;
            try
            {
                NewConn(ref Conn);
                if (Conn.State != ConnectionState.Open)
                {
                    OpenConn(ref Conn);
                }
                dp = new SqlDataAdapter(sql, Conn);
                dp.Fill(ds);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public DataTable GetDataTableFormat(ref SqlConnection Conn, string TableName, string col)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter dp;
            string sql;
            try
            {
                NewConn(ref Conn);
                if (Conn.State != ConnectionState.Open)
                {
                    OpenConn(ref Conn);
                }

                sql = "select " + col + " from " + TableName + " where 1<>1";
                dp = new SqlDataAdapter(sql, Conn);
                dp.Fill(ds, TableName);
                return ds.Tables[TableName];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }


      
        public int InsertTable(ref SqlConnection Conn, DataTable dt)
        {
            int i;
            int k;
            ArrayList list = new ArrayList();

            string sql;
            try
            {
                sql = "insert into " + dt.TableName + " (";
                for (k = 0; k < dt.Columns.Count; k++)
                {
                    sql += dt.Columns[k].ColumnName + ",";
                }

                sql = sql.Substring(0, sql.Length - 1);
                sql += ") Values(";

                for (i = 0; i < dt.Rows.Count; i++)
                {
                    for (k = 0; k < dt.Columns.Count; k++)
                    {
                        sql += GetValue(dt.Columns[k].DataType, dt.Rows[i][k].ToString()) + ",";
                    }
                    sql = sql.Substring(0, sql.Length - 1);
                    sql += ")";
                    list.Add(sql);
                }

                return ExecuteTransaction(ref Conn, list);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {

            }
        }

        public DataTable RowAdd(ref DataTable dt, ArrayList list)
        {
            int i;
            try
            {
                DataRow dr = dt.NewRow();
                for (i = 0; i < list.Count; i++)
                {
                    dr[i] = list[i].ToString();
                }
                dt.Rows.Add(dr);
                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }



        public string FirstCol(ref SqlConnection Conn, string sql)
        {
            object obj;
            SqlCommand cmd = new SqlCommand();
            try
            {
                NewConn(ref Conn);
                if (Conn.State != ConnectionState.Open)
                {
                    OpenConn(ref Conn);
                }
                cmd.Connection = Conn;
                cmd.CommandText = sql;
                obj = cmd.ExecuteScalar();
                if (obj == null)
                {
                    return "";
                }
                else
                {
                    return obj.ToString();
                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
            }

        }


        public int ExecuteSQl(ref SqlConnection Conn, string sql)
        {
            int i;
            SqlCommand cmd = new SqlCommand();
            try
            {
                NewConn(ref Conn);
                if (Conn.State != ConnectionState.Open)
                {
                    OpenConn(ref Conn);
                }
                cmd.Connection = Conn;
                cmd.CommandText = sql;
                i = cmd.ExecuteNonQuery();
                return i;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
            }
        }


        public SqlDataReader GetReader(ref SqlConnection Conn, string sql)
        {
            SqlDataReader dr;
            SqlCommand cmd = new SqlCommand();
            try
            {
                NewConn(ref Conn);
                if (Conn.State != ConnectionState.Open)
                {
                    OpenConn(ref Conn);
                }
                cmd.Connection = Conn;
                cmd.CommandText = sql;
                dr = cmd.ExecuteReader();
                return dr;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
            }
        }

        public int ExecuteTransaction(ref SqlConnection Conn, ArrayList list)
        {
            SqlTransaction Trans;
            SqlCommand cmd = new SqlCommand();

            NewConn(ref Conn);
            if (Conn.State != ConnectionState.Open)
            {
                OpenConn(ref Conn);
            }

            Trans = Conn.BeginTransaction();
            cmd.Transaction = Trans;
            int i = 0, iResult = 0;
            cmd.Connection = Conn;

            try
            {
                for (i = 0; i < list.Count; i++)
                {
                    cmd.CommandText = list[i].ToString();
                    iResult = cmd.ExecuteNonQuery();
                }
                Trans.Commit();
                return iResult;
            }
            catch (Exception ex)
            {
                Trans.Rollback();
                throw ex;
            }
            finally
            {
                Trans.Dispose();
                cmd.Dispose();
            }
        }



        public int ExecuteList(ref SqlConnection Conn, ArrayList list)
        {

            SqlCommand cmd = new SqlCommand();
            int i = 0, iResult = 0;

            NewConn(ref Conn);
            if (Conn.State != ConnectionState.Open)
            {
                OpenConn(ref Conn);
            }
            cmd.Connection = Conn;
            try
            {
                for (i = 0; i < list.Count; i++)
                {
                    cmd.CommandText = list[i].ToString();
                    iResult = cmd.ExecuteNonQuery();
                }
                return iResult;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
            }
        }


        public string GetValue(System.Type DataType, string Value)
        {
            if (DataType == System.Type.GetType("System.String") ||
                DataType == System.Type.GetType("System.DateTime"))
            {
                return "'" + Value + "'";
            }
            else if (DataType == System.Type.GetType("System.DBNull"))
            {
                return "null";
            }
            else if (DataType == System.Type.GetType("System.Int32") ||
                DataType == System.Type.GetType("System.Decimal") ||
                DataType == System.Type.GetType("System.Int64") ||
                DataType == System.Type.GetType("System.Double"))
            {
                return Value;
            }
            return "";
        }

        public string PageList(int size, string funClick, int countNum, int currentPage)
        {
            int pageCount = 0;
            int stepNum = 3;
            int pageRoot = 1;
            string pageStr = "";
            int pageSize = size;
            if (countNum % pageSize == 0)
            {
                pageCount = countNum / pageSize;
            }
            else
            {
                pageCount = countNum / pageSize + 1;
            }
            pageStr = "<div id='pager'>分页：" + currentPage.ToString() + "/" + pageCount.ToString() + "页";
            if (currentPage - stepNum > 1)
            {
                pageRoot = currentPage - stepNum;
            }
            int pageFoot = pageCount;
            if (currentPage + stepNum < pageCount)
            {
                pageFoot = currentPage + stepNum;
            }
            if (pageRoot == 1)
            {
                if (currentPage == 1)
                {
                    pageStr += "<font color=888888 face=webdings>9</font></a>";
                    pageStr += "<font color=888888 face=webdings>7</font></a> ";
                }
                else
                {
                    pageStr += "<a href='#page.1' onclick='" + funClick + "(1)' title='首页'><font face=webdings>9</font></a>";
                    pageStr += "<a href='#page." + Convert.ToString(currentPage - 1) + "' onclick='" + funClick + "(" + Convert.ToString(currentPage - 1) + ")' title='上页'><font face=webdings>7</font></a> ";
                }
            }
            else
            {
                pageStr += "<a href='#page.1' onclick='" + funClick + "(1)' title='首页'><font face=webdings>9</font></a>";
                pageStr += "<a href='#page." + Convert.ToString(currentPage - 1) + "' onclick='" + funClick + "(" + Convert.ToString(currentPage - 1) + ")' title='上页'><font face=webdings>7</font></a>...";
            }
            for (int i = pageRoot; i <= pageFoot; i++)
            {
                if (i == currentPage)
                {
                    pageStr += "<font color='red'>[" + i.ToString() + "]</font>&nbsp;";
                }
                else
                {
                    pageStr += "<a href='#page." + i.ToString() + "' onclick='" + funClick + "(" + i.ToString() + ")'>[" + i.ToString() + "]</a>&nbsp;";
                }
                if (i == pageCount)
                    break;
            }
            if (pageFoot == pageCount)
            {
                if (pageCount == currentPage)
                {
                    pageStr += "<font color=888888 face=webdings>8</font></a>";
                    pageStr += "<font color=888888 face=webdings>:</font></a>";
                }
                else
                {
                    pageStr += "<a href='#page." + Convert.ToString(currentPage + 1) + "' onclick='" + funClick + "(" + Convert.ToString(currentPage + 1) + ")' title='下页'><font face=webdings>8</font></a>";
                    pageStr += "<a href='#page." + pageCount.ToString() + "' onclick='" + funClick + "(" + pageCount.ToString() + ")' title='尾页'><font face=webdings>:</font></a>";
                }
            }
            else
            {
                pageStr += "... <a href='#page." + Convert.ToString(currentPage + 1) + "' onclick='" + funClick + "(" + Convert.ToString(currentPage + 1) + ")' title='下页'><font face=webdings>8</font></a>";
                pageStr += "<a href='#page." + pageCount.ToString() + "' onclick='" + funClick + "(" + pageCount.ToString() + ")' title='尾页'><font face=webdings>:</font></a>";
            }
            pageStr += "</div>";
            return pageStr;
        }
    }
